package kr.hkit.summary_java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.myframework.db.DbUtil;
import kr.hkit.summary_java.erp.dto.Department;
//insert, update 같은거..
public class DepartmentDAO implements DaoInterface<Department> {
	
	//연결정보 가지고 오기 
	private Connection connection;
	
	public DepartmentDAO(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void insertItem(Department item) throws SQLException {
		String sql = "insert into department values(?,?,?)"; //?에 인자 넣어줌
//		connection = ConnectionFactory.getConnection(); //하면 불러와질꺼고
		PreparedStatement pstmt = connection.prepareStatement(sql); //이건unchecked exception이라서 그냥 throws
		pstmt.setInt(1, item.getDeptNo()); //1번 인자값에 deptno 넣기
		pstmt.setString(2, item.getDeptName());
		pstmt.setInt(3, item.getFloor());
//		System.out.println(pstmt); //확인차 해봄
//		int res = pstmt.executeUpdate();
//		System.out.println(res); //두가지로. 하나는 성공한거, 하나는 실패한거. 후에 void로 바꿈
		
		pstmt.executeUpdate();
//		pstmt.close();
//		connection.close(); //종료 꼭 해야합니다.
		DbUtil.close(pstmt); //클로즈 확실하게
		
	}

	@Override
	public void deleteItemByNo(Department item) throws SQLException {
		String sql = "delete from department where deptno = ?";
//		connection = ConnectionFactory.getConnection(); //연결~~
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
//		System.out.println(pstmt); //sql문장 제대로 나오는지 확인용
		pstmt.executeUpdate(); //sql문장 제대로됐는지 확인 됐으면 update실행
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItemByNo(Department item) throws SQLException {
		String sql = "update department set deptname = ?, floor=? where deptno=?";
//		connection = ConnectionFactory.getConnection(); //연결~~
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptName());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptNo());
//		System.out.println(pstmt); //확인용
		pstmt.executeUpdate(); //sql문장 제대로됐는지 확인 됐으면 update실행
		DbUtil.close(pstmt);
	}

	@Override
	public Department searchItem(Department item) throws SQLException {
		Department department = null;
		String sql = "select deptno, deptname, floor from department where deptno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo()); //넣어줘야됨!!!
		ResultSet rs = pstmt.executeQuery(); 
		
		if (rs.next()) { //rs는 데이터 위에있음. 릴레이션 목록 아래로 이동해야함
			department = new Department();
			department.setDeptNo(rs.getInt(1));
			department.setDeptName(rs.getString(2));
			department.setFloor(rs.getInt(3));
		} //있으면 department생성. 없으면 null이 리턴
		
		DbUtil.close(rs);
		DbUtil.close(pstmt); //추가
		return department;
	}

	
	@Override //놔두고 새걸로 실험!!
	public ArrayList<Department> listItems() throws SQLException { //arraylist에 담아서 보여줄거임
		ArrayList<Department> list = new ArrayList<>();
		String sql = "select deptno, deptname, floor from department"; 
//		connection = ConnectionFactory.getConnection(); //연결~~
		PreparedStatement pstmt = connection.prepareStatement(sql);
		//select는 결과를 받아와야 되서 resultset이 필요함
		ResultSet rs = pstmt.executeQuery(); //select는 결과필요하니까 executeQuery씀
		
		while(rs.next()){ //다음 레코드가 있느냐? 있으면 true
			list.add(new Department(rs.getInt("deptno"),
					rs.getString("deptname"), 
					rs.getInt("floor"))); //인덱스든 이름이든 상관없음
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return list;
	}
	
	
	//리스트보기 rs에서 바로 이차원배열에 넣기 !! 
		public String[][] listItems2() throws SQLException {
			
			String[][] data = null;
			
			int row = getDeptRowCount(); //행 갯수
			
			String sql = "select deptno, deptname, floor from department";
			
			PreparedStatement pstmt = connection.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			ResultSetMetaData rd = rs.getMetaData(); //메타데이터
			data = new String[row][rd.getColumnCount()];
			
			int rank = 1; // 순위 수정할 것
			
			int i =0;
			while (rs.next()) {
				
				for (int j = 0; j <rd.getColumnCount(); j++) {
					data[i][j] = rs.getString(j+1);
				
					//System.out.println(data[i][j]);
				}
				i++;
			}
			
			DbUtil.close(rs);
			DbUtil.close(pstmt);
			
			return data;
			
		}
		
		
		//출력2 행 알기용
		public int getDeptRowCount() throws SQLException {
			int result = 0;
			
			String sql = "select count(*) from department";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()) {
				result = rs.getInt(1);
			}
			
			DbUtil.close(rs);
			DbUtil.close(pstmt);
			
			return result; //행 갯수 알아냄!! 
			
		}
	
	
}
